import logging

import requests
from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session, session

from commons.BaseResponse import BaseResponse
from config import SMARTPHONE_PREFIX
from datebase import get_db

data_smart_phone_router = APIRouter(prefix="/smartphone", tags=["大屏数据"])


class WarningInfoQueryParams(BaseModel):
    """报警信息查询参数"""
    eqtType: str = Field(..., description="设备类型")
    fsiteNo: str = Field(..., description="站点编号")
    seq: str = Field(..., description="序列号")
    userId: str = Field(..., description="用户ID")


@data_smart_phone_router.get("/getWarningInfo", summary="大屏数据 报警信息")
def getWarningInfo(warn: WarningInfoQueryParams = Query(), db: Session = Depends(get_db)):
    try:
        sql = "select * from PROC_RPT_GETAIRWARRINGMSG( :userId , :eqtType , :seq , :fsiteNo )"
        sqlResult = db.execute(text(sql), {
            "userId": warn.userId,
            "eqtType": warn.eqtType,
            "seq": warn.seq,
            "fsiteNo": warn.fsiteNo,
        }).fetchall()
        return BaseResponse.success(data={"data": sqlResult})
    except Exception as e:
        return BaseResponse.error(message="未找到报警信息")


@data_smart_phone_router.get("/getTenantFno", summary="楼数据")
def getTenantFno(db: Session = Depends(get_db)):
    sql = "select * from ts_tenant "
    res = db.execute(text(sql)).fetchall()
    cameras = [
        dict(
            id=c_id,
            fno=c_fno,
            fname=c_fname,
            flinkman=c_flinkman,
            faddress=c_faddress,
            fapiurl=c_fapiurl,
            fbegindate=c_fbegindate,
            fenddate=c_fenddate,
            fstatus=c_fstatus,
            fupddate=c_fupddate,
            foperator=c_foperator
        ) for
        c_id, c_fno, c_fname, c_flinkman, c_faddress, c_fapiurl, c_fbegindate, c_fenddate, c_fstatus, c_fupddate, c_foperator, *_
        in res
    ]
    if (cameras is None):
        return BaseResponse.error(message="数据库连接异常")
    return BaseResponse.success(data=cameras)


class EqtSearch(BaseModel):
    fpointNo: str = Field(None, description="站点编号")


@data_smart_phone_router.get("/getEqtInvByFpointNo", summary="实验室设备列表")
def getEqtInvByFpointNo(eqt: EqtSearch = Query(), db: Session = Depends(get_db)):
    sql = "select a.feuipmentid,fequipmentbarcode,a.fequipmenttag,fmacno,fpoint_no,"
    sql = sql + " fpanel,fstate,b.fequipmenttype,flinkmac,flinkmark,"
    sql = sql + " fimage,a.fip,a.fport"
    sql = sql + " from s_tc_equipment_inv a,s_ts_equipment_info b "
    sql = sql + " where 1=1 and a.feuipmentid=b.feuipmentid"
    # 必须绑定mac设备才需要显示
    sql = sql + " and flinkmac<>''"
    if eqt.fpointNo is not None:
        sql = sql + " and a.fpoint_no = :fpointNo "
    sql = sql + " order by b.fequipmenttype"

    data = db.execute(text(sql), {
        "fpointNo": eqt.fpointNo,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['feuipmentid'] = str(data[key][0])
        d1['fequipmentno'] = None
        d1['fequipmentbarcode'] = data[key][1]
        d1['fequipmentname'] = data[key][2]
        d1['electricity'] = "0"
        d1['fmacno'] = data[key][3]
        d1['fpointNo'] = data[key][4]
        d1['fpanel'] = data[key][5]
        d1['fstate'] = data[key][6]
        d1['feuipmenttype'] = data[key][7]
        d1['flinkmac'] = data[key][8]
        d1['flinkmark'] = data[key][9]
        d1['fimage'] = data[key][10]
        d1['fip'] = data[key][11]
        d1['fport'] = data[key][12]
        d1['faction'] = "OFF"
        d1['fsiteName'] = "OFF"
        d1['fpointName'] = "OFF"
        d1['chooseFlag'] = "0"
        d1['ftype'] = None
        d1['fpointNo'] = None
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


class ActionSearch(BaseModel):
    feuipmentid: str = Field(None, description="设备编号")
    sceneFlag: str = Field("0", description="标记")


@data_smart_phone_router.get("/getActionListByFeuipmentid", summary="设备控制指令列表")
def get_action_list_by_feuipmentid(action: ActionSearch = Query(), db: Session = Depends(get_db)):
    sql = "select faction,fdescribe,fimage,findex"
    sql = sql + " from s_ts_equipment_action "
    sql = sql + " where 1=1 "
    if action.feuipmentid is not None:
        sql = sql + " and fdictno = :feuipmentid"
    data = db.execute(text(sql), {
        "feuipmentid": action.feuipmentid,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['faction'] = data[key][0]
        d1['fdescribe'] = data[key][1]
        d1['fimage'] = data[key][2]
        d1['findex'] = data[key][3]
        d1['chooseFlag'] = 0
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


class SceneSearch(BaseModel):
    fpointNo: str = Field(None, description="站点编号")


@data_smart_phone_router.get("/getSceneListByFpointNo", summary="触发场景 y_scene_head 查询接口")
def get_scene_list_by_fpointNo(scene: SceneSearch = Query(), db: Session = Depends(get_db)):
    sql = "select a.fsceneno,a.fscenename,a.fsite_no,a.fairitem,a.fscenetype"
    sql = sql + ",a.fteacherno,fwhere,fvalue,fwhere2,fvalue2,fexectime"
    sql = sql + ",fexecweek1,fexecweek2,fexecweek3,fexecweek4,fexecweek5,fexecweek6,fexecweek7"
    sql = sql + ",a.foperator,a.fupddate,a.frange"
    sql = sql + ",b.fpoint_no"
    sql = sql + " from s_tc_scene_head a,s_tc_scene_point b"
    sql = sql + " where a.fscenetype=2"
    sql = sql + " and a.fsceneno=b.fsceneno"
    if scene.fpointNo is not None:
        sql = sql + " and fpoint_no= :fpointNo "
    data = db.execute(text(sql), {
        "fpointNo": scene.fpointNo,
    }).fetchall()

    # sTcSceneItemList 明细列表
    sql_4 = " select c.fsceneno,c.fno,c.fairitem,d.fariname"
    sql_4 = sql_4 + ",d.ffield,c.fwhere,c.fvalue,c.fwhere2,c.fvalue2 "
    sql_4 = sql_4 + " from s_tc_scene_head a"
    sql_4 = sql_4 + " inner join s_tc_scene_point b on a.fsceneno=b.fsceneno"
    sql_4 = sql_4 + " inner join s_tc_scene_item c on a.fsceneno=c.fsceneno"
    sql_4 = sql_4 + " inner join s_ts_airitem d on d.fairitem=c.fairitem"
    sql_4 = sql_4 + " where a.fscenetype=2 "
    if scene.fpointNo is not None:
        sql_4 = sql_4 + " and fpoint_no= :fpointNo "
    sql_4 = sql_4 + " order by b.fsceneno,b.fentryid"
    data_detail4 = db.execute(text(sql_4), {
        "fpointNo": scene.fpointNo,
    }).fetchall()

    # atsPoints 明细列表
    sql_3 = " select b.fentryid,b.fsceneno,b.fpoint_no fpointno "
    sql_3 = sql_3 + " ,c.fpoint_name fpointName"
    sql_3 = sql_3 + " from s_tc_scene_head a"
    sql_3 = sql_3 + " inner join s_tc_scene_point b on a.fsceneno=b.fsceneno"
    sql_3 = sql_3 + " left join a_ts_point c on b.fpoint_no=c.fpoint_no"
    sql_3 = sql_3 + " where a.fscenetype=2 "
    if scene.fpointNo is not None:
        sql_3 = sql_3 + " and b.fpoint_no= :fpointNo "
    sql_3 = sql_3 + " order by b.fsceneno,b.fentryid"
    data_detail = db.execute(text(sql_3), {
        "fpointNo": scene.fpointNo,
    }).fetchall()

    # sTsEquipmentList 明细列表
    sql_2 = " select b.fsceneno,b.fentryid,b.feuipmentid,b.factiontype,b.faction,b.finstructions"
    sql_2 = sql_2 + ",b.fequipmentbarcode,b.fposition,c.fdictname feuipmentname,e.fequipmenttag"
    sql_2 = sql_2 + " from s_tc_scene_head a"
    sql_2 = sql_2 + " inner join s_tc_scene_point s on a.fsceneno=s.fsceneno"
    sql_2 = sql_2 + " inner join s_tc_scene_detail b on a.fsceneno=b.fsceneno"
    sql_2 = sql_2 + " inner join s_ts_equipment_dict c on b.feuipmentid=c.fdictno"
    sql_2 = sql_2 + " left join s_tc_equipment_inv e on e.fequipmentbarcode=b.fequipmentbarcode"
    sql_2 = sql_2 + " where a.fscenetype=2"
    if scene.fpointNo is not None:
        sql_2 = sql_2 + " and s.fpoint_no= :fpointNo "
    sql_2 = sql_2 + " order by b.factiontype,b.fsceneno,b.fentryid"
    data_detail2 = db.execute(text(sql_2), {
        "fpointNo": scene.fpointNo,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsceneno'] = data[key][0]
        d1['fscenename'] = data[key][1]
        d1['fsiteNo'] = data[key][2]
        d1['fscenetype'] = data[key][3]
        d1['fteacherno'] = data[key][4]
        d1['fwhere'] = data[key][5]
        d1['fvalue'] = data[key][6]
        d1['fwhere2'] = data[key][7]
        d1['fvalue2'] = data[key][8]
        d1['fexectime'] = data[key][9]
        d1['fexecweek1'] = data[key][10]
        d1['fexecweek2'] = data[key][11]
        d1['fexecweek3'] = data[key][12]
        d1['fexecweek4'] = data[key][13]
        d1['fexecweek5'] = data[key][14]
        d1['fexecweek6'] = data[key][15]
        d1['fexecweek7'] = data[key][16]
        d1['foperator'] = data[key][17]
        d1['fupddate'] = data[key][18]
        d1['frange'] = data[key][19]
        d1['fpointNo'] = data[key][20]
        d1['fpointName'] = None
        d1['executed'] = "执行中"
        d1['isExecuted'] = ""
        sbillno = data[key][0]

        weekList = list()
        if data[key][2] == "1":
            weekList.append("1")
        if data[key][3] == "1":
            weekList.append("2")
        if data[key][4] == "1":
            weekList.append("3")
        if data[key][5] == "1":
            weekList.append("4")
        if data[key][6] == "1":
            weekList.append("5")
        if data[key][7] == "1":
            weekList.append("6")
        if data[key][8] == "1":
            weekList.append("7")
        print(weekList)
        d1['fexecweekList'] = weekList

        sTcSceneItemList = []
        for j4 in range(len(data_detail4)):
            if (str(data_detail4[j4][0]) == str(sbillno)):
                d4 = {}
                d4['fsceneno'] = data_detail4[j4][0]
                d4['fno'] = data_detail4[j4][1]
                d4['fairitem'] = data_detail4[j4][2]
                d4['fariname'] = data_detail4[j4][3]
                d4['ffield'] = data_detail4[j4][4]
                d4['fwhere'] = data_detail4[j4][5]
                d4['fvalue'] = data_detail4[j4][6]
                d4['fwhere2'] = data_detail4[j4][7]
                d4['fvalue2'] = data_detail4[j4][8]
                sTcSceneItemList.append(d4)

        atsPoints = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][1]) == str(sbillno)):
                d2 = {}
                d2['fentryid'] = data_detail[j1][0]
                d2['fsceneno'] = data_detail[j1][1]
                d2['fpointNo'] = data_detail[j1][2]
                d2['fpointName'] = data_detail[j1][3]
                atsPoints.append(d2)

        sTsEquipmentList = []  # 超标设备
        sTsEquipmentList2 = []  # 达标设备
        for j2 in range(len(data_detail2)):
            if (str(data_detail2[j2][0]) == str(sbillno)):
                d3 = {}
                d3['fsceneno'] = data_detail2[j2][0]
                d3['fentryid'] = data_detail2[j2][1]
                d3['feuipmentid'] = data_detail2[j2][2]
                d3['factiontype'] = data_detail2[j2][3]
                d3['faction'] = data_detail2[j2][4]
                d3['finstructions'] = data_detail2[j2][5]
                d3['fequipmentbarcode'] = data_detail2[j2][6]
                d3['fposition'] = data_detail2[j2][7]
                d3['feuipmentname'] = data_detail2[j2][8]
                d3['fequipmenttag'] = data_detail2[j2][9]
                d3['fimage'] = None

                if int(data_detail2[j2][1]) == 1:
                    sTsEquipmentList.append(d3)
                else:
                    sTsEquipmentList2.append(d3)

        d1["atsPoints"] = atsPoints
        d1["sTsEquipmentList"] = sTsEquipmentList
        d1["sTcSceneItemList"] = sTcSceneItemList
        d1["sTsEquipmentList2"] = sTsEquipmentList2
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


class SchoolSearch(BaseModel):
    fsiteNo: str = Field(None, description="客户编号")


@data_smart_phone_router.get("/getSchoolInfo2", summary="app.客户信息查询")
def getSchoolInfo2(scene: SchoolSearch = Query(), db: Session = Depends(get_db)):
    sql = "select a.fsite_no,a.fsite_name,a.fsite_parent,a.fsite_level,a.fsite_l,"
    sql = sql + " a.fsite_d,a.fengineer,a.fphone,a.ftelno,a.femail,"
    sql = sql + " a.fimage,a.fsite_state,a.fupddate,a.foperator,a.fso2,"
    sql = sql + " a.fno2,a.fco,a.fo3,a.fpm25,a.fpm10,a.fwd,"
    sql = sql + " a.fsd,a.noise,a.faqi,a.faqi_tb,a.faqi_hb,"
    sql = sql + " a.fcontaminants,a.fpm26,a.fpm11,a.fco2,"
    sql = sql + " a.fjq,a.fyjw,a.fwd2,a.fsd2,a.faqi2,"
    sql = sql + " a.fcontaminants2,a.fnetwork_ok,a.fnetwork_no,a.frefresh,"
    sql = sql + " a.fsite_type,a.day_off,a.fbegtime,a.fendtime,a.fimage3d,"
    sql = sql + " a.fboxflag,a.fuseflag,a.flink_man,a.fusetype,a.finvtype,"
    sql = sql + " a.fouttype,a.fapiflag,a.fcourseflag,a.fbegindate,"
    sql = sql + " a.fenddate,a.fcharge,a.fchargeprice,a.fappurl,"
    sql = sql + " a.fappid,a.fappkey,a.fappsecret,b.fsite_name"
    sql = sql + " from a_ts_site a"
    sql = sql + " left join a_ts_site b on a.fsite_parent=b.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and a.fsite_no= :fsiteNo "
    data = db.execute(text(sql), {
        "fsiteNo": scene.fsiteNo,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteNo'] = data[key][0]
        d1['fsiteName'] = data[key][1]
        d1['fsiteParent'] = data[key][2]
        d1['fsiteLevel'] = data[key][3]
        d1['fsiteL'] = data[key][4]
        d1['fsiteD'] = data[key][5]
        d1['fengineer'] = data[key][6]
        d1['fphone'] = data[key][7]
        d1['ftelno'] = data[key][8]
        d1['femail'] = data[key][9]
        d1['fimage'] = data[key][10]
        d1['fsiteState'] = data[key][11]
        d1['fupddate'] = data[key][12]
        d1['foperator'] = data[key][13]
        d1['fso2'] = data[key][14]
        d1['fno2'] = data[key][15]
        d1['fco'] = data[key][16]
        d1['fo3'] = data[key][17]
        d1['fpm25'] = data[key][18]
        d1['fpm10'] = data[key][19]
        d1['fwd'] = data[key][20]
        d1['fsd'] = data[key][21]
        d1['noise'] = data[key][22]
        d1['faqi'] = data[key][23]
        d1['faqiTb'] = data[key][24]
        d1['faqiHb'] = data[key][25]
        d1['fcontaminants'] = data[key][26]
        d1['fpm26'] = data[key][27]
        d1['fpm11'] = data[key][28]
        d1['fco2'] = data[key][29]
        d1['fjq'] = data[key][30]
        d1['fyjw'] = data[key][31]
        d1['fwd2'] = data[key][32]
        d1['fsd2'] = data[key][33]
        d1['faqi2'] = data[key][34]
        d1['fcontaminants2'] = data[key][35]
        d1['fnetworkOk'] = data[key][36]
        d1['fnetworkNo'] = data[key][37]
        d1['frefresh'] = data[key][38]
        d1['fsitetype'] = data[key][39]
        d1['dayoff'] = data[key][40]
        d1['fbegtime'] = data[key][41]
        d1['fendtime'] = data[key][42]
        d1['fimage3d'] = data[key][43]
        d1['fboxflag'] = data[key][44]
        d1['fuseflag'] = data[key][45]
        d1['flinkMan'] = data[key][46]
        d1['fusetype'] = data[key][47]
        d1['finvtype'] = data[key][48]
        d1['fouttype'] = data[key][49]
        d1['fapiflag'] = data[key][50]
        d1['fcourseflag'] = data[key][51]
        d1['fbeginDate'] = data[key][52]
        d1['fendDate'] = data[key][53]
        d1['fcharge'] = data[key][54]
        d1['fchargeprice'] = data[key][55]
        d1['fappurl'] = data[key][56]
        d1['fappid'] = data[key][57]
        d1['fappkey'] = data[key][58]
        d1['fappsecret'] = data[key][59]
        d1['parentFsiteName'] = data[key][60]
        d1['pointPicture'] = None
        d1['pointMessage'] = None
        d1['fexecweekList'] = None
        atsSiteList = []
        d1['atsSiteList'] = atsSiteList
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


class EqtSearch(BaseModel):
    fequipmentbarcode: str = Field(None, description="设备编号")


@data_smart_phone_router.get("/getEqtStatus", summary="读取实验室设备状态--返回假数据")
def getEqtStatus():
    data_list = []
    d1 = {}
    d1['online'] = 1
    d1['power'] = "0"
    d1['percent'] = None
    data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


class AqiSearch(BaseModel):
    fpointNo: str = Field(None, description="站点编号")


@data_smart_phone_router.get("/getAqiByFpointNo", summary="通过教室编号获取aqi数据(feuipmenttype，6为室外，7为室内)")
def getAqiByFpointNo(aqi: AqiSearch = Query(), db: Session = Depends(get_db)):
    sql = (
        "select b.fequipmentno, b.fequipmentname, b.fequipmenttype, to_char(c.time, 'HH24:MI:SS') ftime, to_char(c.time, 'yyyy-mm-dd HH24:MI:SS') ftime2, "
        " pm25, pm10, co, o3, no2, so2 , co2, hcho, tvoc, t, h, pm1"
        " from s_tc_equipment_inv a, s_ts_equipment_info b, a_tp_point c"
        " where a.fpoint_no = :fpointNo "
        " and a.feuipmentid = b.feuipmentid"
        " and a.fmacno = c.id and to_char(c.time, 'yyyy-mm-dd') = to_char(now(), 'yyyy-mm-dd')"
        " order by time desc"
        " limit 12"
    )
    sqlResult = db.execute(text(sql), {
        "fpointNo": aqi.fpointNo,
    }).fetchall()
    resultData = []
    if (len(sqlResult) > 0):
        for key in range(len(sqlResult)):
            fequipmentno = sqlResult[key][0]
            fequipmentname = sqlResult[key][1]
            fequipmenttype = sqlResult[key][2]
            ftime = sqlResult[key][3]
            ftime2 = sqlResult[key][4]
            pm25 = sqlResult[key][5]
            pm10 = sqlResult[key][6]
            co = sqlResult[key][7]
            o3 = sqlResult[key][8]
            no2 = sqlResult[key][9]
            so2 = sqlResult[key][10]
            co2 = sqlResult[key][11]
            hcho = sqlResult[key][12]
            tvoc = sqlResult[key][13]
            t = sqlResult[key][14]
            h = sqlResult[key][15]
            pm1 = sqlResult[key][16]
            item = {"fequipmentno": fequipmentno, "fequipmentname": fequipmentname, "fequipmenttype": fequipmenttype,
                    "ftime": ftime, "ftime2": ftime2,
                    "pm25": pm25, "pm10": pm10, "co": co, "o3": o3, "no2": no2, "so2": so2, "co2": co2, "hcho": hcho,
                    "tvoc": tvoc, "t": t, "h": h, "pm1": pm1}
            resultData.append(item)
    commonResult = {"list": resultData}
    return BaseResponse.success(data=commonResult)


@data_smart_phone_router.get("/getEleNotificationByFsiteNo", summary="根据fsiteNo获取用电通报信息")
def getEleNotificationByFsiteNo(warn: WarningInfoQueryParams, db: Session = Depends(get_db)):
    sql = "select * from proc_rpt_getsmartwarringmsg( :userId , :eqtType , :seq , :fsiteNo )"
    sqlResult = db.execute(text(sql), {
        "userId": warn.userId,
        "eqtType": warn.eqtType,
        "seq": warn.seq,
        "fsiteNo": warn.fsiteNo,
    }).fetchall()
    resultData = []
    if sqlResult is not None and len(sqlResult) > 0:
        for key in range(len(sqlResult)):
            fentryid = sqlResult[key][0]
            ftitle = sqlResult[key][1]
            ftime = sqlResult[key][2]
            fpoint = sqlResult[key][3]
            fmeg = sqlResult[key][4]
            item = {"fentryid": fentryid, "ftitle": ftitle, "ftime": ftime, "fpoint": fpoint, "fmeg": fmeg}
            resultData.append(item)
    commonResult = {"list": resultData}
    return BaseResponse.success(data=commonResult)


@data_smart_phone_router.get("/getStoreyEqtDataByFlayerNo",
                             summary="数据大屏  根据楼层编号获取楼层内各教室统计 调用java接口")
def getStoreyEqtDataByFlayerNo(flayerNo: str, db: Session = Depends(get_db)):
    paramers = {"flayerNo": flayerNo}
    try:
        response = requests.get(SMARTPHONE_PREFIX + "/getStoreyEqtDataByFlayerNo", params=paramers)
        jsondata = response.json()
        if response.status_code == 200:
            code = jsondata["code"]
            data = jsondata["data"]
            msg = jsondata["msg"]
            return BaseResponse.success(data=data)
        else:
            return BaseResponse.error(code=response.status_code, message="调用失败")
    except Exception as e:
        logging.error(">>>>> 网络连接redis失败")
        return BaseResponse.error()
